// Copyright (c) Appeon. All rights reserved.
// Licensed under the Apache License, Version 2.0 (the "License"); you may not use this file except in compliance with the License. 
// You may obtain a copy of the License at http://www.apache.org/licenses/LICENSE-2.0.

using System.Collections.Generic;
using System.Linq;
using System.Text;
using JetBrains.Annotations;
using SnapObjects.Commons;

namespace SnapObjects.Data
{
    internal abstract class UpdateSqlGenerator : IUpdateSqlGenerator
    {
        public UpdateSqlGenerator(ISqlGenerationHelper sqlGenerationHelper)
        {
            this.SqlGenerationHelper = sqlGenerationHelper;
        }

        public ISqlGenerationHelper SqlGenerationHelper { get; }

        public virtual ResultSetMapping AppendRawUpdateSql(
            [NotNull] StringBuilder sqlStringBuilder,
            [NotNull] string rawUpdateSql,
            int sqlPosition)
        {
            sqlStringBuilder.Append(rawUpdateSql);
            sqlStringBuilder.Append(this.SqlGenerationHelper.StatementTerminator).AppendLine();

            return this.AppendSelectAffectedCountSql(sqlStringBuilder, null, null, 0);
        }

        public virtual ResultSetMapping AppendInsertSql(
            [NotNull] StringBuilder sqlStringBuilder,
            [NotNull] ModelUpdateInfo modelUpdateInfo,
            int sqlPosition)
        {
            Check.NotNull(sqlStringBuilder, nameof(sqlStringBuilder));
            Check.NotNull(modelUpdateInfo, nameof(modelUpdateInfo));

            var name = modelUpdateInfo.Table;
            var schema = modelUpdateInfo.Schema;
            var operations = modelUpdateInfo.ColumnModifications;

            var writeOperations = operations.Where(o => o.IsWrite).ToList();
            var readOperations = operations.Where(o => o.IsRead).ToList();

            this.AppendInsertSql(sqlStringBuilder, name, schema, writeOperations);

            if (readOperations.Count > 0)
            {
                var keyOperations = operations.Where(o => o.IsKey && !(!o.IsIdentity && o.IsRead)).ToList();

                if (keyOperations.Count() == 0)
                {
                    var identityOperation = operations.Where(o => o.IsIdentity).FirstOrDefault();

                    if (identityOperation != null)
                    {
                        keyOperations.Add(identityOperation);
                    }
                }

                return this.AppendSelectAffectedSql(sqlStringBuilder, name, schema, readOperations, keyOperations, sqlPosition);
            }
            else
            {
                return this.AppendSelectAffectedCountSql(sqlStringBuilder, name, schema, sqlPosition);
            }
        }

        public virtual ResultSetMapping AppendDeleteSql(
            [NotNull] StringBuilder sqlStringBuilder,
            [NotNull] ModelUpdateInfo modelUpdateInfo,
            int sqlPosition)
        {
            Check.NotNull(sqlStringBuilder, nameof(sqlStringBuilder));
            Check.NotNull(modelUpdateInfo, nameof(modelUpdateInfo));

            var name = modelUpdateInfo.Table;
            var schema = modelUpdateInfo.Schema;
            var conditionOperations = modelUpdateInfo.ColumnModifications.Where(o => o.IsCondition).ToList();

            this.AppendDeleteSql(sqlStringBuilder, name, schema, conditionOperations);

            return this.AppendSelectAffectedCountSql(sqlStringBuilder, name, schema, sqlPosition);
        }

        public virtual ResultSetMapping AppendUpdateSql(
            [NotNull] StringBuilder sqlStringBuilder,
            [NotNull] ModelUpdateInfo modelUpdateInfo,
            int sqlPosition)
        {
            Check.NotNull(sqlStringBuilder, nameof(sqlStringBuilder));
            Check.NotNull(modelUpdateInfo, nameof(modelUpdateInfo));

            var name = modelUpdateInfo.Table;
            var schema = modelUpdateInfo.Schema;
            var operations = modelUpdateInfo.ColumnModifications;

            var writeOperations = operations.Where(o => o.IsWrite).ToList();
            var conditionOperations = operations.Where(o => o.IsCondition).ToList();
            var readOperations = operations.Where(o => o.IsRead).ToList();

            if (writeOperations.Count > 0)
            {
                this.AppendUpdateSql(sqlStringBuilder, name, schema, writeOperations, conditionOperations);
                /*if (readOperations.Count > 0)
                {
                    var keyOperations = operations.Where(o => o.IsKey).ToList();

                    return AppendSelectAffectedSql(sqlStringBuilder, name, schema, readOperations, keyOperations, sqlPosition);
                }*/
            }

            return this.AppendSelectAffectedCountSql(sqlStringBuilder, name, schema, sqlPosition);
        }

        protected virtual void AppendInsertSql(
            [NotNull] StringBuilder sqlStringBuilder,
            [NotNull] string name,
            [CanBeNull] string schema,
            [NotNull] IReadOnlyList<ColumnModification> writeOperations)
        {
            Check.NotNull(sqlStringBuilder, nameof(sqlStringBuilder));
            Check.NotEmpty(name, nameof(name));
            Check.NotNull(writeOperations, nameof(writeOperations));

            this.AppendInsertHeader(sqlStringBuilder, name, schema, writeOperations);
            this.AppendValuesHeader(sqlStringBuilder, writeOperations);
            this.AppendValues(sqlStringBuilder, writeOperations);
            sqlStringBuilder.Append(this.SqlGenerationHelper.StatementTerminator).AppendLine();
        }

        protected virtual void AppendInsertHeader(
            [NotNull] StringBuilder sqlStringBuilder,
            [NotNull] string name,
            [CanBeNull] string schema,
            [NotNull] IReadOnlyList<ColumnModification> operations)
        {
            Check.NotNull(sqlStringBuilder, nameof(sqlStringBuilder));
            Check.NotEmpty(name, nameof(name));
            Check.NotNull(operations, nameof(operations));

            sqlStringBuilder.Append("INSERT INTO ");
            this.SqlGenerationHelper.DelimitIdentifier(sqlStringBuilder, name, schema);

            if (operations.Count > 0)
            {
                sqlStringBuilder
                    .Append(" (")
                    .AppendValues(operations,
                        this.SqlGenerationHelper,
                        (sb, o, helper) => helper.DelimitIdentifier(sb, o.MappedColumn))
                    .Append(")");
            }
        }

        protected virtual void AppendValuesHeader(
            [NotNull] StringBuilder sqlStringBuilder,
            [NotNull] IReadOnlyList<ColumnModification> operations)
        {
            Check.NotNull(sqlStringBuilder, nameof(sqlStringBuilder));
            Check.NotNull(operations, nameof(operations));

            sqlStringBuilder.AppendLine();
            sqlStringBuilder.Append(operations.Count > 0 ? "VALUES " : "DEFAULT VALUES");
        }

        protected virtual void AppendValues(
            [NotNull] StringBuilder sqlStringBuilder,
            [NotNull] IReadOnlyList<ColumnModification> operations)
        {
            Check.NotNull(sqlStringBuilder, nameof(sqlStringBuilder));
            Check.NotNull(operations, nameof(operations));

            if (operations.Count > 0)
            {
                sqlStringBuilder
                    .Append("(")
                    .AppendValues(
                        operations,
                        this.SqlGenerationHelper,
                        (sb, o, helper) =>
                        {
                            if (o.IsWrite)
                            {
                                helper.GenerateParameterName(sb, o.ParameterName);
                            }
                            else
                            {
                                sb.Append("DEFAULT");
                            }
                        })
                    .Append(")");
            }
        }

        protected virtual ResultSetMapping AppendSelectAffectedSql(
            [NotNull] StringBuilder sqlStringBuilder,
            [NotNull] string name,
            [CanBeNull] string schema,
            [NotNull] IReadOnlyList<ColumnModification> readOperations,
            [NotNull] IReadOnlyList<ColumnModification> conditionOperations,
            int sqlPosition)
        {
            Check.NotNull(sqlStringBuilder, nameof(sqlStringBuilder));
            Check.NotEmpty(name, nameof(name));
            Check.NotNull(readOperations, nameof(readOperations));
            Check.NotNull(conditionOperations, nameof(conditionOperations));

            this.AppendSelectHeader(sqlStringBuilder, readOperations);
            this.AppendFromClause(sqlStringBuilder, name, schema);

            this.AppendWhereAffectedClause(sqlStringBuilder, conditionOperations);
            sqlStringBuilder.Append(this.SqlGenerationHelper.StatementTerminator).AppendLine()
                .AppendLine();

            return ResultSetMapping.SelectAndLastInResultSet;
        }

        protected virtual void AppendDeleteSql(
            [NotNull] StringBuilder sqlStringBuilder,
            [NotNull] string name,
            [CanBeNull] string schema,
            [NotNull] IReadOnlyList<ColumnModification> conditionOperations)
        {
            Check.NotNull(sqlStringBuilder, nameof(sqlStringBuilder));
            Check.NotEmpty(name, nameof(name));
            Check.NotNull(conditionOperations, nameof(conditionOperations));

            this.AppendDeleteHeader(sqlStringBuilder, name, schema);
            this.AppendWhereClause(sqlStringBuilder, conditionOperations);
            sqlStringBuilder.Append(this.SqlGenerationHelper.StatementTerminator).AppendLine();
        }

        protected virtual void AppendUpdateSql(
            [NotNull] StringBuilder sqlStringBuilder,
            [NotNull] string name,
            [CanBeNull] string schema,
            [NotNull] IReadOnlyList<ColumnModification> writeOperations,
            [NotNull] IReadOnlyList<ColumnModification> conditionOperations)
        {
            Check.NotNull(sqlStringBuilder, nameof(sqlStringBuilder));
            Check.NotEmpty(name, nameof(name));
            Check.NotEmpty(writeOperations, nameof(writeOperations));
            Check.NotNull(conditionOperations, nameof(conditionOperations));

            this.AppendUpdateHeader(sqlStringBuilder, name, schema, writeOperations);
            this.AppendWhereClause(sqlStringBuilder, conditionOperations);
            sqlStringBuilder.Append(this.SqlGenerationHelper.StatementTerminator).AppendLine();
        }

        protected virtual void AppendUpdateHeader(
            [NotNull] StringBuilder sqlStringBuilder,
            [NotNull] string name,
            [CanBeNull] string schema,
            [NotNull] IReadOnlyList<ColumnModification> operations)
        {
            Check.NotNull(sqlStringBuilder, nameof(sqlStringBuilder));
            Check.NotEmpty(name, nameof(name));
            Check.NotNull(operations, nameof(operations));

            sqlStringBuilder.Append("UPDATE ");
            this.SqlGenerationHelper.DelimitIdentifier(sqlStringBuilder, name, schema);
            sqlStringBuilder.Append(" SET ")
                .AppendValues(
                    operations,
                    this.SqlGenerationHelper,
                    (sb, o, helper) =>
                    {
                        helper.DelimitIdentifier(sb, o.MappedColumn);
                        sb.Append(" = ");
                        helper.GenerateParameterName(sb, o.ParameterName);
                    });
        }

        protected virtual void AppendWhereClause(
            [NotNull] StringBuilder sqlStringBuilder,
            [NotNull] IReadOnlyList<ColumnModification> operations)
        {
            Check.NotNull(sqlStringBuilder, nameof(sqlStringBuilder));
            Check.NotNull(operations, nameof(operations));

            if (operations.Count > 0)
            {
                sqlStringBuilder
                    .Append(" ").AppendLine()
                    .Append("WHERE ")
                    .AppendValues(operations, (sb, v) => this.AppendWhereCondition(sb, v, v.UseOriginalValueParameter), " AND ");
            }
        }

        protected virtual void AppendWhereAffectedClause(
            [NotNull] StringBuilder sqlStringBuilder,
            [NotNull] IReadOnlyList<ColumnModification> operations)
        {
            Check.NotNull(sqlStringBuilder, nameof(sqlStringBuilder));
            Check.NotNull(operations, nameof(operations));

            sqlStringBuilder
                .AppendLine()
                .Append("WHERE ");

            this.AppendRowsAffectedWhereCondition(sqlStringBuilder, 1);

            if (operations.Count > 0)
            {
                sqlStringBuilder
                    .Append(" AND ")
                    .AppendValues(operations, (sb, v) =>
                    {
                        if (v.IsKey || v.IsIdentity)
                        {
                            if (v.IsRead)
                            {
                                this.AppendIdentityWhereCondition(sb, v);
                            }
                            else
                            {
                                // AppendWhereCondition(sb, v, v.UseOriginalValueParameter);
                                this.AppendWhereAffectedCondition(sb, v, v.UseOriginalValueParameter);
                            }
                        }
                    }, " AND ");
            }
        }

        protected abstract void AppendRowsAffectedWhereCondition([NotNull] StringBuilder sqlStringBuilder, int expectedRowsAffected);

        protected abstract void AppendIdentityWhereCondition([NotNull] StringBuilder sqlStringBuilder, [NotNull] ColumnModification columnModification);

        protected virtual void AppendWhereCondition(
            [NotNull] StringBuilder sqlStringBuilder,
            [NotNull] ColumnModification columnModification,
            bool useOriginalValue)
        {
            Check.NotNull(sqlStringBuilder, nameof(sqlStringBuilder));
            Check.NotNull(columnModification, nameof(columnModification));

            this.SqlGenerationHelper.DelimitIdentifier(sqlStringBuilder, columnModification.MappedColumn);

            var parameterValue = useOriginalValue
                ? columnModification.OriginalValue
                : columnModification.CurrentValue;

            if (parameterValue == null)
            {
                sqlStringBuilder.Append(" IS NULL");
            }
            else
            {
                sqlStringBuilder.Append(" = ");
                this.SqlGenerationHelper.GenerateParameterName(sqlStringBuilder, useOriginalValue
                    ? columnModification.OriginalParameterName
                    : columnModification.ParameterName);
            }
        }

        protected virtual void AppendWhereAffectedCondition(
            [NotNull] StringBuilder sqlStringBuilder,
            [NotNull] ColumnModification columnModification,
            bool useOriginalValue)
        {
            this.AppendWhereCondition(sqlStringBuilder, columnModification, useOriginalValue);
        }

        protected virtual void AppendDeleteHeader(
            [NotNull] StringBuilder sqlStringBuilder,
            [NotNull] string name,
            [CanBeNull] string schema)
        {
            Check.NotNull(sqlStringBuilder, nameof(sqlStringBuilder));
            Check.NotEmpty(name, nameof(name));

            sqlStringBuilder.Append("DELETE FROM ");
            this.SqlGenerationHelper.DelimitIdentifier(sqlStringBuilder, name, schema);
        }

        protected virtual void AppendSelectHeader(
            [NotNull] StringBuilder sqlStringBuilder,
            [NotNull] IReadOnlyList<ColumnModification> operations)
        {
            Check.NotNull(sqlStringBuilder, nameof(sqlStringBuilder));
            Check.NotNull(operations, nameof(operations));

            sqlStringBuilder
                .Append("SELECT ")
                .AppendValues(
                    operations,
                    this.SqlGenerationHelper,
                    (sb, o, helper) => helper.DelimitIdentifier(sb, o.MappedColumn));
        }

        protected virtual void AppendFromClause(
            [NotNull] StringBuilder sqlStringBuilder,
            [NotNull] string name,
            [CanBeNull] string schema)
        {
            Check.NotNull(sqlStringBuilder, nameof(sqlStringBuilder));
            Check.NotEmpty(name, nameof(name));

            sqlStringBuilder
                .AppendLine()
                .Append("FROM ");
            this.SqlGenerationHelper.DelimitIdentifier(sqlStringBuilder, name, schema);
        }

        protected virtual ResultSetMapping AppendSelectAffectedCountSql(
            [NotNull] StringBuilder sqlStringBuilder,
            [NotNull] string name,
            [CanBeNull] string schema,
            int sqlPosition)
        {
            return ResultSetMapping.NoResultSet;
        }

        public virtual void AppendBatchHeader(StringBuilder commandStringBuilder)
        {

        }

        public virtual string GenerateNextSequenceValueSql(string name, string schema)
        {
            var sqlStringBuilder = new StringBuilder();
            this.AppendNextSequenceValueSql(sqlStringBuilder, name, schema);
            return sqlStringBuilder.ToString();
        }

        public virtual void AppendNextSequenceValueSql(StringBuilder sqlStringBuilder, string name, string schema)
        {
            sqlStringBuilder.Append("SELECT NEXT VALUE FOR ");
            this.SqlGenerationHelper.DelimitIdentifier(sqlStringBuilder, Check.NotNull(name, nameof(name)), schema);
        }
    }
}
